class Admin
  
  #zoekt alle producten gegeven de zoek parameters
  def self.find_products_for_admin_by_parameters(params)
    conditions = {:string => "", :parameters =>[]}
    if(!params[:search].nil?)
      conditions = Admin.create_conditions(params[:search])
    end
    sort = Admin.create_sort(params[:sort],params[:sort_on])
    if(conditions[:string] != "")
      conditions[:string] = 'WHERE '+conditions[:string]
    end
    query = ['SELECT products.* FROM products LEFT JOIN product_categories ON products.id = product_categories.product_id LEFT JOIN categories ON product_categories.category_id = categories.id '+conditions[:string]+' GROUP BY products.id '+sort[:string]]
    return Product.find_by_sql(query+conditions[:parameters]+sort[:parameters])
  end
  
  #zoekt alle gebruikers aan de hand van de parameters
  def self.find_users_for_admin_by_parameters(params)
    conditions = {:string => "", :parameters =>[]}
    if(!params[:search].nil?)
      conditions = Admin.create_conditions(params[:search])
    end
    if(conditions[:string] != "")
      conditions[:string] = 'WHERE '+conditions[:string]
    end
    sort = Admin.create_sort(params[:sort],params[:sort_on])
    query = 'SELECT users.* FROM users LEFT JOIN addresses ON addresses.user_id = users.id '+conditions[:string]+' GROUP BY users.id '+sort[:string]
    input = [query]
    input += conditions[:parameters]+sort[:parameters]
    return User.find_by_sql(input)
  end
  
  #zoekt de producten gegeven zoek parameters en maakt extra kolommen aan voor de totaal aantal verkocht, totale omzet en totale omzet met huidige prijs per product
  def self.get_products_statistics(params)
    conditions = {:string => "", :parameters =>[]}
    if(!params[:search].nil?)
      conditions = Admin.create_conditions(params[:search])
    end
    if(conditions[:string] != "")
      conditions[:string] = 'WHERE orders.status != 5 AND '+conditions[:string]
    else
      conditions[:string] = 'WHERE orders.status != 5'
    end
    parameters = conditions[:parameters]
    sort = Admin.create_sort(params[:sort],params[:sort_on])
    parameters += sort[:parameters]
    query = 'SELECT products.*, SUM(order_items.quantity) AS total_quantity, SUM(order_items.quantity*order_items.original_price) AS total_price, SUM(order_items.quantity*products.price) AS current_total_price FROM products LEFT JOIN product_categories ON products.id = product_categories.product_id LEFT JOIN categories ON product_categories.category_id = categories.id LEFT JOIN order_items ON products.id = order_items.product_id LEFT JOIN orders ON order_items.order_id = orders.id '+conditions[:string]+' GROUP BY products.id '+sort[:string]
    
    return Product.find_by_sql([query]+parameters)
  end
  
  #zoekt de orders gegeven zoek parameters
  def self.find_orders_for_admin_by_parameters(params)
    conditions = {:string => "", :parameters =>[]}
    if(!params[:search].nil?)
      conditions = Admin.create_conditions(params[:search])
    end
    if(conditions[:string] != "")
      conditions[:string] = 'WHERE '+conditions[:string]
    end
    parameters = conditions[:parameters]
    sort = Admin.create_sort(params[:sort],params[:sort_on])
    parameters += sort[:parameters]
    query = 'SELECT orders.* FROM orders LEFT JOIN users ON orders.user_id = users.id '+conditions[:string]+' '+sort[:string]
    return Order.find_by_sql([query]+parameters)
  end
  
  #geeft de where conditions terug voor een sql query
  def self.create_conditions(parameters)
    if(!parameters.nil?)
      and_conditions = Admin.create_and_conditions(parameters[:and])
      or_conditions = Admin.create_or_conditions(parameters[:or])
      if(and_conditions[:string] != "" && or_conditions[:string] != "")
        return{:string => and_conditions[:string]+' AND '+or_conditions[:string],:parameters => and_conditions[:parameters]+or_conditions[:parameters]}
      else
        return{:string => and_conditions[:string]+or_conditions[:string],:parameters => and_conditions[:parameters]+or_conditions[:parameters]}
      end
    else
      return {:string => "", parameters => []}
    end
    return {:string => "", parameters => []}
  end
  
  #geeft een hash terug met daarin de conditions string en een array met de parameters
  def self.create_and_conditions(parameters)
    if(!parameters.nil?)
      and_conditions = []
      conditions_parameters = []
      parameters.each do |column,hash_or_value|
          case column
          when :DATE
            date_hash = Admin.create_date_conditions(hash_or_value)
            if(date_hash[:string] != "")
              and_conditions <<= date_hash[:string]
              conditions_parameters += date_hash[:parameters]
            end
          when :CATEGORY
            category_hash = Admin.create_category_conditions(hash_or_value)
            if(category_hash[:string] != "")
              and_conditions <<= category_hash[:string]
              conditions_parameters += category_hash[:parameters]
            end
          else
            hash_or_value.each do |compare_tag,value|
              if(value != "")
              print "-------------------\nBefore: #{compare_tag}\n"
              print "After: #{Admin.parse_compare_tag_to_value(compare_tag)}\n------------------\n"
                and_conditions <<= column+' '+Admin.parse_compare_tag_to_value(compare_tag)+' ?'
                if(compare_tag == 'like' || compare_tag == :like)
                  value += '%'
                end
                conditions_parameters <<= value
              end
            end
        end
      end
      conditions_hash = {:string => and_conditions.join(' AND '), :parameters =>conditions_parameters}
      return conditions_hash
    else
      return {:string => "",:parameters => []} 
    end
  end
  
  #geeft een hash terug waar de date voor SQL in de where query wordt gezet 
  def self.create_date_conditions(date_hash)
    conditions = []
    parameters = []
    date_hash.each do |column,value_hash|
      value_hash.each do |compare_tag, date|
        if(self.check_date?(date))
          conditions<<= column+' '+Admin.parse_compare_tag_to_value(compare_tag)+' ?'
          parameters <<= date[:year]+'-'+date[:month]+'-'+date[:day]
        end
      end
    end
    if(conditions.size>0)
      return {:string => conditions.join(" AND "), :parameters => parameters}
    else
      return {:string => "", :parameters => []}
    end
  end
  
  #checkt of er een complete datum is ingevuld in de view
  def self.check_date?(date)
    if(date == nil)
      return false
    elsif(date[:year] == nil || date[:year] == "")
      return false
    elsif(date[:month] == nil || date[:month] == "")
      return false
    elsif(date[:day] == nil || date[:day] == "")
      return false
    else
      return true
    end
    
  end

  
  #geeft een hash terug, met daarin de conditions string en een array met de parameters
  def self.create_or_conditions(parameters)
    if(!parameters.nil?)
      or_conditions = []
      conditions_parameters = []
      parameters.each do |column,hash|
        hash.each do |separate_key,value_hash|
          value_hash.each do |compare_tag,value|
            if(value != "")
              or_conditions <<= column+' '+Admin.parse_compare_tag_to_value(compare_tag)+' ?'
              if(compare_tag == 'like' || compare_tag == :like)
                value += '%'
              end
              conditions_parameters <<= value
            end
          end
        end
      end
      conditions_hash = {:string => "", :parameters =>[]}
      if(or_conditions.size>0)
        conditions_hash[:string] = '('+or_conditions.join(' OR ')+')'
      end
      conditions_hash[:parameters] = conditions_parameters
      return conditions_hash
    else
      return {:string => "",:parameters => []} 
    end
  end
  
  #geeft een hash terug waarin de conditions staan om de producten binnen de categorie te krijgen en de parameters
  def self.create_category_conditions(category_name)
    category = Category.first(:conditions => ['name COLLATE NOCASE = ?',category_name])
    if(category != nil)
      return {:string => "categories.left >= ? AND categories.right <= ?", :parameters => [category.left,category.right]}
    else
      return {:string => "",:parameters => []} 
    end
  end
  
  #zet een tag om in een vergelijkingssymbool voor een sql query
  def self.parse_compare_tag_to_value(compare_tag)
    value = ''
    case compare_tag
      when 'eq' then value = '='
      when :eq then value = '='      
      when 'like' then value = 'LIKE'
      when :like then value = 'LIKE'
      when 'gt' then value = '>='
      when :gt then value = '>='
      when 'st' then value = '<='
      when :st then value = '<='
    end
    return value
  end
  
  #maakt een hash aan om de ORDER aan te geven in een sql query
  def self.create_sort(sort,sort_on)
    if(sort != nil && sort != "" && sort_on != nil && sort_on != "")
    sort = get_sort_tag(sort)
    sort_on = Admin.remove_sql_injection(sort_on)
      return {:string => "ORDER BY #{sort_on} #{sort}",:parameters => []}
    end
    return {:string => "", :parameters => []}
  end
  
  #filtert de sort tag van SQL injecties
  def self.get_sort_tag(sort_tag)
    case sort_tag
      when 'ASC'
        return 'ASC'
      when 'DESC'
        return 'DESC'
      else
        return 'ASC'
    end
  end
  
  def self.remove_sql_injection(sort_on)
    case sort_on
      when 'orders.id' then return sort_on
      when 'users.name' then return sort_on
      when 'orders.created_at' then return sort_on
      when 'orders.status' then return sort_on
      when 'orders.payment_type' then return sort_on
      when 'products.name' then return sort_on
      when 'products.stock' then return sort_on
      when 'products.price' then return sort_on
      when 'total_quantity' then return sort_on
      when 'total_price' then return sort_on
      when 'users.name' then return sort_on
      when 'users.email' then return sort_on
      when 'users.created_at' then return sort_on
      when 'users.is_admin' then return sort_on
      else return 'id'
    end
    return sort_on
  end
end